﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Reflection;
using System.IO;
using System.Diagnostics;
using System.Collections;
using Excel = Microsoft.Office.Interop.Excel;

namespace template_dll
{
    public partial class ChildForm : Form
    {
        public ChildForm()
        {
            InitializeComponent();
        }

        private const string namechcol = "Флаг";
        private SqlConnection conn;
        private DataTable dt_rep = new DataTable();
        private delegate void Deleg();
        private Deleg del;
        private object month;
        private object manager;
        
        public static bool DisplayForm(System.Windows.Forms.Form theMDIParent)
        {
            ChildForm f = new ChildForm();
            f.MdiParent = theMDIParent;
            f.WindowState = FormWindowState.Maximized;
            f.Show();
            return true;
        }

        private string GetServ(string filename)
        {
            string str = "";
            using (StreamReader streamReader = new StreamReader(filename))
            {
                while (!streamReader.EndOfStream)
                {
                    str += streamReader.ReadLine();
                }
            }
            return str;
        }

        private void ChildForm_Load(object sender, EventArgs e)
        {
            try
            {
                conn = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=PharmOLAPDW;Integrated Security=True");
                conn.Open();

                NewItem ni1 = new NewItem();
                ni1.ParamComment = "Все";
                ni1.ParamID = null;
                comboBox2.Items.Add(ni1);
                comboBox2.SelectedIndex = 0;
                DateTime currentdate = DateTime.Today.AddDays(-1);
                dateTimePicker1.Value = currentdate;
                //DataTable dt = (new SqlStorProc()).SqlProcExecWithQueryOneTable(conn, "pas_MonthSel", 30, null);
                //foreach (DataRow row in dt.Rows)
                //{
                //    NewItem ni = new NewItem();
                //    ni.ParamID = row[0];
                //    ni.ParamComment = row[1].ToString();
                //    comboBox1.Items.Add(ni);
                //    if (currentdate.Year == Convert.ToDateTime(row[0]).Year && currentdate.Month == Convert.ToDateTime(row[0]).Month)
                //        comboBox1.SelectedIndex = comboBox1.Items.Count - 1;
                //}

                DataTable dt1 = (new SqlStorProc()).SqlProcExecWithQueryOneTable(conn, "pas_CategManager", 30, null);
                foreach (DataRow row in dt1.Rows)
                {
                    NewItem ni = new NewItem();
                    ni.ParamID = row[0];
                    ni.ParamComment = row[1].ToString();
                    comboBox2.Items.Add(ni);
                }

            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void StartCreateRep(object sender, EventArgs e)
        {
            manager = (comboBox2.SelectedItem as NewItem).ParamID;
            month = dateTimePicker1.Value.Date;//(comboBox1.SelectedItem as NewItem).ParamID;
            dataGridView1.DataSource = null;
            dt_rep.Clear();
            del = new Deleg(CreateRep);
            this.Text += " Формируется";
            IAsyncResult asyncRes = del.BeginInvoke(new AsyncCallback(CallBackFunc), null); //
            panel1.Enabled = false;
            dataGridView1.Enabled = false;
        }

        private void CallBackFunc(IAsyncResult aRes)
        {
            del.EndInvoke(aRes);
            panel1.Invoke((Func<bool>)(() => panel1.Enabled = true));
            dataGridView1.Invoke((Func<bool>)(() => dataGridView1.Enabled = true));
            this.Invoke((Func<string>)(() => this.Text = this.Text.Replace(" Формируется", "")));
            dataGridView1.Invoke((Func<object>)(() => dataGridView1.DataSource = dt_rep.DefaultView));
        }

        private void CreateRep()
        {
            if (conn.State == ConnectionState.Closed) conn.Open();
            try
            {
                SqlStorProc.sp_params[] param = new SqlStorProc.sp_params[2];
                param[0].name = "@dayrep";
                param[0].value = month;
                param[1].name = "@manid";
                param[1].value = manager;
                dt_rep = (new SqlStorProc()).SqlProcExecWithQueryOneTable(conn,"pas_DAC_DinamCategMan",300,param);
                conn.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
                conn.Close();
            }
        }

        private SqlDataReader RepProc(SqlConnection sqlcon)//метод для вызова хранимки
        {
            SqlCommand sqlcom = sqlcon.CreateCommand();
            sqlcom.CommandType = CommandType.StoredProcedure;
            sqlcom.CommandText = @"sp_name"; //имя хранимой процедуры
            sqlcom.CommandTimeout = 300;

            return sqlcom.ExecuteReader();
        }

        private void ExpToExcel(object sender, EventArgs e)
        {
            SaveFileDialog sfd = new SaveFileDialog();
            sfd.FileName = "csv-file";
            sfd.Filter = "Файл CSV | *.csv";
            if (sfd.ShowDialog() == DialogResult.OK)
                (new ExpToExcel()).SaveDGVToCSVfile(sfd.FileName.ToString(), dataGridView1, true);
            
        }

        private void dataGridView1_DataSourceChanged(object sender, EventArgs e)
        {
            int perc = 5;
            foreach (DataGridViewColumn col in dataGridView1.Columns)
            {
                col.HeaderCell.Style.BackColor = Color.SkyBlue;
                if (col.Index <= 2)
                {
                    col.Frozen = true;
                    col.DefaultCellStyle.BackColor = Color.SkyBlue;
                }
                else
                    if (col.Index == 3 || col.Index == 4 || col.Index == 13) col.DefaultCellStyle.Format = "p";
                    else
                        if (col.Index == perc)
                        {
                            col.DefaultCellStyle.Format = "p";
                            perc += 3;
                        }
                        else col.DefaultCellStyle.Format = "0";
                
            }
        }

        private void dataGridView1_CellPainting(object sender, DataGridViewCellPaintingEventArgs e)
        {
            if (e.ColumnIndex > 2 && e.RowIndex > -1)
            {
                if (Convert.ToDouble(dataGridView1[e.ColumnIndex, e.RowIndex].Value) < 0)
                    dataGridView1[e.ColumnIndex, e.RowIndex].Style.ForeColor = Color.Red;
                if (e.ColumnIndex == 17 || e.ColumnIndex == 14)
                {
                    if (Convert.ToDouble(dataGridView1[e.ColumnIndex, e.RowIndex].Value) < 1)
                        dataGridView1[e.ColumnIndex, e.RowIndex].Style.ForeColor = Color.Red;
                }
            }
        }

        private void button2_Click(object sender, EventArgs e)
        { 
            if (dataGridView1.DataSource != null)
            {

                string fname = "динамика - отделения.xlsx";
                System.Globalization.CultureInfo oldCI = System.Threading.Thread.CurrentThread.CurrentCulture;
                System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

                Hashtable myHashtable = new Hashtable();
                ExpToExcel ete = new ExpToExcel();
                myHashtable = ete.CheckExcellProcesses(myHashtable);

                Excel.Application exapp = new Excel.Application();
                Excel.Workbook exwb = exapp.Workbooks.Add(System.Reflection.Missing.Value);
                Excel.Worksheet exws = (Excel.Worksheet)exwb.Sheets[1];
                exapp.DisplayAlerts = false;
                string filename = "";
                try
                {
                    if (filename == "")
                    {
                        FileDialog sfd = new SaveFileDialog();
                        sfd.FileName = fname;
                        sfd.Filter = "Excel | *.xlsx";
                        if (sfd.ShowDialog() == DialogResult.OK) filename = sfd.FileName;
                        sfd = null;
                    }
                    if (Path.GetExtension(filename) == "xlsx") Path.ChangeExtension(filename, "xlsx");
                    if (filename != "")
                    {
                        foreach (DataGridViewColumn col in dataGridView1.Columns)
                        {
                            if (col.Index < 3)
                            {
                                exws.get_Range(exws.Cells[1, col.Index + 1], exws.Cells[1, col.Index + 1]).Value2 = col.Name;
                                exws.get_Range(exws.Cells[1, col.Index + 1], exws.Cells[1, col.Index + 1]).Borders.Color = Excel.XlRgbColor.rgbBlack;
                                exws.get_Range(exws.Cells[1, col.Index + 1], exws.Cells[1, col.Index + 1]).Cells.Interior.Color = Excel.XlRgbColor.rgbSkyBlue;
                            }
                            if (col.Index >= 3)
                            {
                                exws.get_Range(exws.Cells[1, col.Index + 1], exws.Cells[1, col.Index + 1]).Value2 = col.Name;
                                exws.get_Range(exws.Cells[1, col.Index + 1], exws.Cells[1, col.Index + 1]).Borders.Color = Excel.XlRgbColor.rgbBlack;
                                exws.get_Range(exws.Cells[1, col.Index + 1], exws.Cells[1, col.Index + 1]).Cells.Interior.Color = Excel.XlRgbColor.rgbSkyBlue;
                            }
                        }
                        
                        foreach (DataGridViewRow row in dataGridView1.Rows)
                        {
                            int perc = 5;
                            if (row.Index > 0)
                            {
                                foreach (DataGridViewCell cell in row.Cells)
                                {
                                    int r = row.Index + 1;
                                    int c = cell.ColumnIndex + 1;
                                    
                                    if (cell.ColumnIndex < 3)
                                    {
                                        exws.get_Range(exws.Cells[r, c], exws.Cells[r, c]).Value2 = cell.Value;
                                        exws.get_Range(exws.Cells[r, c], exws.Cells[r, c]).Borders.Color = Excel.XlRgbColor.rgbBlack;
                                        exws.get_Range(exws.Cells[r, c], exws.Cells[r, c]).Cells.Interior.Color = Excel.XlRgbColor.rgbSkyBlue;
                                    }
                                    else
                                    {
                                        exws.get_Range(exws.Cells[r, c], exws.Cells[r, c]).Value2 = cell.Value;
                                        exws.get_Range(exws.Cells[r, c], exws.Cells[r, c]).Cells.Font.Color = SetColor(cell.Value,cell.ColumnIndex);
                                        exws.get_Range(exws.Cells[r, c], exws.Cells[r, c]).Borders.Color = Excel.XlRgbColor.rgbBlack;

                                        if (cell.ColumnIndex == 3 || cell.ColumnIndex == 4 || cell.ColumnIndex == 13)
                                            exws.get_Range(exws.Cells[r, c], exws.Cells[r, c]).NumberFormat = "##0.00%";
                                        else
                                            if (cell.ColumnIndex == perc)
                                            {
                                                exws.get_Range(exws.Cells[r, c], exws.Cells[r, c]).NumberFormat = "##0.00%";
                                                perc += 3;
                                            }
                                            else
                                                exws.get_Range(exws.Cells[r, c], exws.Cells[r, c]).NumberFormat = "### ### ### ### ##0";
                                    }
                                }
                            }
                        }                       

                        exws.Application.ActiveWindow.SplitRow = 1;
                        exws.Application.ActiveWindow.SplitColumn = 1;
                        exws.Application.ActiveWindow.SplitColumn = 2;
                        exws.Application.ActiveWindow.SplitColumn = 3;
                        exws.Application.ActiveWindow.FreezePanes = true;
                        exws.get_Range(exws.Cells[1, 1], exws.Cells[dataGridView1.RowCount, dataGridView1.ColumnCount]).EntireColumn.AutoFit();
                        exwb.SaveAs(filename, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
                        exwb.Close(Type.Missing, Type.Missing, Type.Missing);
                        MessageBox.Show("Файл сохранен");
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                finally
                {
                    exapp.Quit();
                    ete.KillExcel(myHashtable);
                    System.Threading.Thread.CurrentThread.CurrentCulture = oldCI;
                }
            }
        }

        private Excel.XlRgbColor SetColor(object value,int colnum)
        {
            Excel.XlRgbColor color = Excel.XlRgbColor.rgbBlack;
            if (value != DBNull.Value)
            {
                if (Convert.ToDouble(value) < 0)
                    color = Excel.XlRgbColor.rgbRed;
                if (colnum == 17 || colnum == 14)
                    if (Convert.ToDouble(value) < 1)
                        color = Excel.XlRgbColor.rgbRed;
            }

            return color;
        }

        private Excel.XlRgbColor SetColor(Color col)
        {
            Excel.XlRgbColor color = Excel.XlRgbColor.rgbBlack;
            if(col == Color.Red)
                color = Excel.XlRgbColor.rgbRed;
            
            return color;
        }
    }
}
